<!---
Copyright: (c) 2006 The MachBlog Authors
Authors: Matt Woodward (mpwoodward@gmail.com) & Peter J. Farrell (pjf@maestropublishing.com)

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License. 
You may obtain a copy of the License at 

http://www.apache.org/licenses/LICENSE-2.0 

Unless required by applicable law or agreed to in writing, software 
distributed under the License is distributed on an "AS IS" BASIS, 
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 
See the License for the specific language governing permissions and 
limitations under the License.

$Id: EntryGateway.cfc 1184 2007-06-25 21:26:37Z pfarrell $

Notes:
--->
<cfcomponent 
	displayname="EntryGateway" 
	output="false" 
	extends="EntryGateway" 
	hint="EntryGateway for MachBlog (MySQL)">
	
	<cffunction name="init" access="public" output="false" returntype="EntryGateway" 
			hint="Constructor for this CFC">
		<cfreturn this />
	</cffunction>
	
	<cffunction name="getEntries" access="public" output="false" returntype="query" 
			hint="Gets mulitple entries by various criteria">
		<cfargument name="getBy" type="string" required="false" default="all" />
		<cfargument name="numDays" type="numeric" required="false" default="0" />
		<cfargument name="categoryId" type="string" required="false" default="" />
		<cfargument name="orderBy" type="string" required="false" 
				default="machblog_entry.dt_posted DESC">
		<cfargument name="numToReturn" type="numeric" required="false" default="-1">
		<cfargument name="searchTerms" type="string" required="false" default="" />
		<cfargument name="getActiveOnly" type="boolean" required="false" default="true" />
		<cfargument name="month" type="numeric" required="false" default="0" />
		<cfargument name="year" type="numeric" required="false" default="0" />
		<cfargument name="currentPosts" type="boolean" required="false" default="false" />
		
		<cfset var getEntries = "" />
		<cfset var getCategories = "" />
		<cfset var getCommentCount = "" />
		<cfset var getTrackbackCount = "" />
		<cfset var whereClause = "" />
		<cfset var startDate = "" />
		<cfset var endDate = "" />
		<cfset var getEntryIds = "" />
		<cfset var entryIds = "" />
		
		<cfif arguments.getBy NEQ "all">
			<cfswitch expression="#arguments.getBy#">
				<cfcase value="homePage">
					<!--- get the last N days using the numDaysOnHomePage property --->
					<cfset startDate = DateAdd("d", -1 * arguments.numDays, Now()) />
					<cfset startDate = CreateDateTime(DatePart("yyyy", startDate), 
														DatePart("m", startDate), 
														DatePart("d", startDate), 
														00, 00, 00) />
					<cfset endDate = Now() />
					
					<!--- convert the dates to epochs to match what's in the db --->
					<cfset startDate = getResourceBundleFacade().getLocaleUtils().toEpoch(startDate) />
					<cfset endDate = getResourceBundleFacade().getLocaleUtils().toEpoch(endDate) />
					
					<cfset whereClause = "WHERE machblog_entry.dt_posted >= " & startDate 
											& " AND machblog_entry.dt_posted <= " & endDate />
				</cfcase>
				
				<cfcase value="category">
					<!--- get the ids for the category --->
					<cfquery name="getEntryIds" datasource="#getDatasource().getDsn()#" 
							username="#getDatasource().getUserName()#" 
							password="#getDatasource().getPassword()#">
						SELECT 	entry_id 
						FROM 	machblog_entry_category 
						WHERE 	category_id = <cfqueryparam value="#arguments.categoryId#" 
															cfsqltype="cf_sql_char" maxlength="35" />
					</cfquery>
					
					<cfif getEntryIds.RecordCount GT 0>
						<cfset whereClause = "WHERE machblog_entry.entry_id IN (" & 
												QuotedValueList(getEntryIds.entry_id) & ")" />
					<cfelse>
						<cfset whereClause = "WHERE 1 = 0" />
					</cfif>
				</cfcase>
				
				<cfcase value="month">
					<cfset startDate = createDateTime(arguments.year, arguments.month, 1, 00, 00, 00) />
					<cfset endDate = createDateTime(arguments.year, arguments.month, daysInMonth(startDate), 23, 59, 59) />
					<cfset startDate = getResourceBundleFacade().getLocaleUtils().toEpoch(startDate) />
					<cfset endDate = getResourceBundleFacade().getLocaleUtils().toEpoch(endDate) />
					<cfset whereClause = "WHERE machblog_entry.dt_posted >= " & startDate 
											& " AND machblog_entry.dt_posted <= " & endDate />
				</cfcase>
			</cfswitch>
		</cfif>
		
		<cftry>
			<cfquery name="getEntries" datasource="#getDatasource().getDsn()#" 
					username="#getDatasource().getUserName()#" password="#getDatasource().getPassword()#" 
					maxrows="#arguments.numToReturn#">
				SELECT 	machblog_entry.*, 
						cb.first_name AS cbfn, 
						cb.last_name AS cbln, 
						mb.first_name AS mbfn, 
						mb.last_name AS mbln 
				FROM 	machblog_entry 
				LEFT OUTER JOIN machblog_user AS cb 
					ON machblog_entry.created_by_id = cb.user_id 
				LEFT OUTER JOIN machblog_user AS mb 
					ON machblog_entry.modified_by_id = mb.user_id 
				<cfif whereClause NEQ "">
				#PreserveSingleQuotes(whereClause)# 
				<cfelseif arguments.searchTerms NEQ "">
					WHERE (machblog_entry.title LIKE '%#arguments.searchTerms#%' 
							OR machblog_entry.body LIKE '%#arguments.searchTerms#%' 
							OR machblog_entry.more_body LIKE '%#arguments.searchTerms#%') 
				<cfelse>
					WHERE 1 = 1 
				</cfif>
				<cfif arguments.getBy NEQ "all" OR arguments.currentPosts>
					AND machblog_entry.dt_posted <= <cfqueryparam value="#getResourceBundleFacade().getLocaleUtils().toEpoch(Now())#" 
																	cfsqltype="cf_sql_bigint" /> 
				</cfif>
				<cfif arguments.getActiveOnly>
					AND machblog_entry.is_active = <cfqueryparam value="1" cfsqltype="cf_sql_tinyint" /> 
				</cfif>
				ORDER BY #orderBy#
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
		
		<cfset QueryAddColumn(getEntries, "category_ids", ArrayNew(1)) />
		<cfset QueryAddColumn(getEntries, "category_names", ArrayNew(1)) />
		<cfset QueryAddColumn(getEntries, "comment_count", ArrayNew(1)) />
		<cfset QueryAddColumn(getEntries, "trackback_count", ArrayNew(1)) />
		
		<cfloop query="getEntries">
			<cftry>
				<cfquery name="getCategories" datasource="#getDatasource().getDsn()#" 
						username="#getDatasource().getUserName()#" 
						password="#getDatasource().getPassword()#">
					SELECT 	machblog_category.category_id, machblog_category.category_name 
					FROM 	machblog_category 
					LEFT OUTER JOIN machblog_entry_category 
						ON machblog_category.category_id = machblog_entry_category.category_id 
					WHERE 	machblog_entry_category.entry_id = 
								<cfqueryparam value="#getEntries.entry_id#" cfsqltype="cf_sql_char" 
											maxlength="35" /> 
					ORDER BY category_name ASC
				</cfquery>
				<cfcatch type="database">
					<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
				</cfcatch>
			</cftry>
			
			<cftry>
				<cfquery name="getCommentCount" datasource="#getDatasource().getDsn()#" 
						username="#getDatasource().getUserName()#" 
						password="#getDatasource().getPassword()#">
					SELECT COUNT(comment_id) AS comment_count 
					FROM machblog_comment 
					WHERE entry_id = <cfqueryparam value="#getEntries.entry_id#" 
													cfsqltype="cf_sql_char" maxlength="35" /> 
					AND is_active = <cfqueryparam value="1" cfsqltype="cf_sql_tinyint" /> 
					AND (subscription_only = <cfqueryparam value="0" cfsqltype="cf_sql_tinyint" /> 
							OR subscription_only IS NULL)
				</cfquery>
				<cfcatch type="database">
					<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
				</cfcatch>
			</cftry>
			
			<cftry>
				<cfquery name="getTrackbackCount" datasource="#getDatasource().getDsn()#" 
						username="#getDatasource().getUserName()#" password="#getDatasource().getPassword()#">
					SELECT COUNT(trackback_id) AS trackback_count 
					FROM machblog_trackback 
					WHERE entry_id = <cfqueryparam value="#getEntries.entry_id#" cfsqltype="cf_sql_char" 
													maxlength="35" /> 
					AND is_active = <cfqueryparam value="1" cfsqltype="cf_sql_tinyint" />
				</cfquery>
				<cfcatch type="database">
					<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
				</cfcatch>
			</cftry>
			
			<cfset QuerySetCell(getEntries, "category_ids", ValueList(getCategories.category_id), 
									getEntries.CurrentRow) />
			<cfset QuerySetCell(getEntries, "category_names", ValueList(getCategories.category_name), 
									getEntries.CurrentRow) />
			<cfset QuerySetCell(getEntries, "comment_count", getCommentCount.comment_count, 
									getEntries.CurrentRow) />
			<cfset QuerySetCell(getEntries, "trackback_count", getTrackbackCount.trackback_count, 
									getEntries.CurrentRow) />
		</cfloop>
		
		<cfreturn getEntries />
	</cffunction>
	
	<cffunction name="getEntryTitleById" access="public" output="false" returntype="string" 
			hint="Returns an entry title based on the ID passed in">
		<cfargument name="entryId" type="uuid" required="true" />
		
		<cfset var getEntryTitle = "" />
		<cfset var entryTitle = "" />
		
		<cftry>
			<cfquery name="getEntryTitle" datasource="#getDatasource().getDsn()#" 
						username="#getDatasource().getUserName()#" 
						password="#getDatasource().getPassword()#">
				SELECT title 
				FROM machblog_entry 
				WHERE entry_id = <cfqueryparam value="#arguments.entryId#" cfsqltype="cf_sql_char" 
												maxlength="35" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
		
		<cfif IsQuery(getEntryTitle) AND getEntryTitle.RecordCount GT 0>
			<cfset entryTitle = getEntryTitle.title />
		</cfif>
		
		<cfreturn entryTitle />
	</cffunction>
	
	<cffunction name="getArchiveList" access="public" output="false" returntype="array" 
			hint="Returns an array containing month numbers and the number of entries per month">
		<cfargument name="numMonths" type="numeric" required="true" hint="The number of months to return" />
		
		<cfset var getArchiveCount = 0 />
		<cfset var startDate = 0 />
		<cfset var startEpochs = arrayNew(1) />
		<cfset var archiveList = arrayNew(2) />
		<cfset var i = 0 />
		
		<!--- create epochs for each of the archive month start dates --->
		<cfloop index="i" from="#arguments.numMonths * -1#" to="0" step="1">
			<cfset startDate = dateAdd("m", i, createDateTime(datePart("yyyy", now()), datePart("m", now()), 1, 0, 0, 0)) />
			<cfset arrayAppend(startEpochs, getResourceBundleFacade().getLocaleUtils().toEpoch(startDate)) />
		</cfloop>
		
		<!--- stick the epoch for the start of one month in the future in the array so we have an end epoch for the last 
				month of the archives --->
		<cfset startDate = Now() />
		<cfset arrayAppend(startEpochs, getResourceBundleFacade().getLocaleUtils().toEpoch(startDate)) />
		
		<!--- get the counts for each of the months --->
		<cfloop index="i" from="1" to="#arrayLen(startEpochs) - 1#" step="1">
			<cftry>
				<cfquery name="getArchiveCount" datasource="#getDatasource().getDsn()#" 
						username="#getDatasource().getUserName()#" password="#getDatasource().getPassword()#">
					SELECT COUNT(machblog_entry.entry_id) AS entry_count
					FROM machblog_entry 
					WHERE dt_posted >= <cfqueryparam value="#startEpochs[i]#" cfsqltype="cf_sql_bigint" /> 
					AND dt_posted < <cfqueryparam value="#startEpochs[i + 1]#" cfsqltype="cf_sql_bigint" />
				</cfquery>
				
				<cfset archiveList[i][1] = startEpochs[i] />
				<cfset archiveList[i][2] = getArchiveCount.entry_count />
				
				<cfcatch type="database">
					<cfthrow type="application" message="Database error" detail="An error occurred in EntryGateway.cfc, method getArchiveList: #CFCATCH.Detail#" />
				</cfcatch>
			</cftry>
		</cfloop>
		
		<cfreturn archiveList />
	</cffunction>
	
	<cffunction name="incrementViews" access="public" output="false" returntype="void" 
			hint="Increments the number of times the entry has been viewed">
		<cfargument name="entryId" type="uuid" required="true" />
		
		<cfset var updateNumViews = "" />
		
		<cftry>
			<cfquery name="getEntryCount" datasource="#getDatasource().getDsn()#" 
						username="#getDatasource().getUserName()#" 
						password="#getDatasource().getPassword()#">
				UPDATE 	machblog_entry 
				SET 	num_views = num_views + 1 
				WHERE 	entry_id = <cfqueryparam value="#arguments.entryId#" cfsqltype="cf_sql_char" 
													maxlength="35" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="An error occurred in EntryGateway.cfc, method incrementViews: #CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
	</cffunction>
	
	<cffunction name="getEntryCount" access="public" output="false" returntype="numeric" 
			hint="Returns the total number of entries in the blog">
		<cfargument name="userId" type="string" required="false" default="" />
		
		<cfset var getEntryCount = "" />
		<cfset var entryCount = 0 />
		
		<cftry>
			<cfquery name="getEntryCount" datasource="#getDatasource().getDsn()#" 
						username="#getDatasource().getUserName()#" 
						password="#getDatasource().getPassword()#">
				SELECT COUNT(entry_id) AS entry_count
				FROM machblog_entry 
				<cfif arguments.userId NEQ "">
				WHERE created_by_id = <cfqueryparam value="#arguments.userId#" cfsqltype="cf_sql_char" maxlength="35" />
				</cfif>
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="An error occurred in EntryGateway.cfc, method getEntryCount: #CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
		
		<cfif IsQuery(getEntryCount) AND getEntryCount.RecordCount GT 0>
			<cfset entryCount = getEntryCount.entry_count />
		</cfif>
		
		<cfreturn entryCount />
	</cffunction>
	
	<cffunction name="getUnnotifiedEntries" access="public" output="false" returntype="query" 
			hint="Returns a query object containing the entries for which subscribers haven't been notified">
		<cfset var entries = QueryNew("entry_id,title") />
		
		<cftry>
			<cfquery name="entries" datasource="#getDatasource().getDsn()#" 
					username="#getDatasource().getUserName()#" password="#getDatasource().getPassword()#">
				SELECT 	entry_id, title 
				FROM 	machblog_entry 
				WHERE 	subscriber_email_sent IS NULL 
				OR 		subscriber_email_sent = <cfqueryparam value="0" cfsqltype="cf_sql_tinyint" />
			</cfquery>
			<cfcatch type="database">
				<!--- just return an empty query object --->
			</cfcatch>
		</cftry>
		
		<cfreturn entries />
	</cffunction>
	
	<cffunction name="updateSubscriberEmailFlag" access="public" output="false" returntype="void" 
			hint="Updates the subscriber email flag for an entry id">
		<cfargument name="entryId" type="uuid" required="true" />
		
		<cfset var updateFlag = "" />
		
		<cftry>
			<cfquery name="updateFlag" datasource="#getDatasource().getDsn()#" 
					username="#getDatasource().getUserName()#" password="#getDatasource().getPassword()#">
				UPDATE 	machblog_entry 
				SET 	subscriber_email_sent = <cfqueryparam value="1" cfsqltype="cf_sql_tinyint" /> 
				WHERE 	entry_id = <cfqueryparam value="#arguments.entryId#" cfsqltype="cf_sql_char" />
			</cfquery>
			<cfcatch type="database">
				<!--- no biggie if this bombs since it runs on every home page hit --->
			</cfcatch>
		</cftry>
	</cffunction>
</cfcomponent>
